package com.dy.yunying.biz.dao.clickhouse3399.impl;

import com.dy.yunying.api.dto.AdDataDto;
import com.dy.yunying.api.vo.AdDataVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;

/**
 * @author ：lile
 * @date ：2021/6/17 14:05
 * @description：
 * @modified By：
 */
@Component
@Slf4j
public class AdDataDao {

	@Resource(name = "clickhouseTemplate")
	private JdbcTemplate clickhouseTemplate;


	public List<AdDataVo> selectAdDataSource(AdDataDto req) {
		StringBuilder sql = getSql(req);
		log.info("广告数据.sql:[{}]", sql.toString());


		List<AdDataVo> data = new ArrayList<>();

		SqlRowSet rs = clickhouseTemplate.queryForRowSet(sql.toString());

		List<String> columns = Arrays.asList(rs.getMetaData().getColumnNames());

		//遍历 ResultSet
		while (rs.next()) {
			//组装成对象 添加到结果集
			AdDataVo item = ObjByRow(rs, columns, "period", req.getShowRatio());
			data.add(item);
		}

		return data;
	}

	private AdDataVo ObjByRow(SqlRowSet rs, List<String> columns, String type, Integer showRatio) {
		AdDataVo vo = new AdDataVo();
		if (type.equals("period")) {
			vo.setPeriod(rs.getString("period"));
		}
		if (columns.contains("cost"))
			vo.setCost(rs.getBigDecimal("cost"));

		//投放人id
		if (columns.contains("investor") && StringUtils.isNotBlank(rs.getString("investor"))) {
			vo.setInvestor(rs.getString("investor"));
		} else {
			vo.setInvestor("-");
		}

		//投放人名称
		if (columns.contains("investorName") && StringUtils.isNotBlank(rs.getString("investorName"))) {
			vo.setInvestorName(rs.getString("investorName"));
		} else {
			vo.setInvestorName("-");
		}


		//部门维度 部门名称
		if (columns.contains("deptName") && StringUtils.isNotBlank(rs.getString("deptName"))) {
			vo.setDeptName(rs.getString("deptName"));
		} else {
			vo.setDeptName("-");
		}

		//部门维度 部门id
		if (columns.contains("deptId") && StringUtils.isNotBlank(rs.getString("deptId"))) {
			vo.setDeptId(rs.getString("deptId"));
		} else {
			vo.setDeptId("-");
		}
		//组别维度 组别名称
		if (columns.contains("userGroupName") && StringUtils.isNotBlank(rs.getString("userGroupName"))) {
			vo.setUserGroupName(rs.getString("userGroupName"));
		} else {
			vo.setUserGroupName("-");
		}
		//组别维度 组别id
		if (columns.contains("userGroupId") && StringUtils.isNotBlank(rs.getString("userGroupId"))) {
			vo.setUserGroupId(rs.getString("userGroupId"));
		} else {
			vo.setUserGroupId("-");
		}

		//主渠道
		if (columns.contains("parentchl") && StringUtils.isNotBlank(rs.getString("parentchl"))) {
			vo.setParentchl(rs.getString("parentchl"));
		} else {
			vo.setParentchl("-");
		}

		//父游戏 id
		if (columns.contains("pgid"))
			vo.setPgid(rs.getLong("pgid"));


		//系统
		if (columns.contains("os"))
			vo.setOs(rs.getInt("os"));


		// 新增设备充值数
		Integer divideNum = Integer.valueOf(
				rs.getInt("usrnamenums")
		);

		if (rs.getBigDecimal("rudecost") == null) {
			vo.setRudecost(BigDecimal.ZERO);
		} else {
			vo.setRudecost(rs.getBigDecimal("rudecost").intValue() == 0.000 ? BigDecimal.ZERO : rs.getBigDecimal("rudecost"));
		}

		BigDecimal cost = BigDecimal.ZERO;
		if (rs.getBigDecimal("cost") == null) {
			cost = BigDecimal.ZERO;
			vo.setCost(cost);
		} else {
			cost = rs.getBigDecimal("cost").intValue() == 0.000 ? BigDecimal.ZERO : rs.getBigDecimal("cost");
			vo.setCost(cost);
		}

		if (columns.contains("usrnamenums"))
			vo.setUsrnamenums(rs.getInt("usrnamenums"));


		//os
		if (columns.contains("os"))
			vo.setOs(rs.getInt("os"));

		//新增设备成本
		if (divideNum == 0 || divideNum == null) {
			vo.setDeviceCose(BigDecimal.ZERO);
		} else {
			vo.setDeviceCose(cost.divide(new BigDecimal(divideNum), 2, RoundingMode.HALF_UP));
		}

		//新增充值金额  newdevicefees
		if (columns.contains("newdevicefees") || columns.contains("newdevicesharfee")) {
			//不显示分成前数据
			if (showRatio == 0) {
				vo.setNewPayAmount(rs.getBigDecimal("newdevicefees") == null ? BigDecimal.ZERO : rs.getBigDecimal("newdevicefees"));
			} else {
				vo.setNewPayAmount(rs.getBigDecimal("newdevicesharfee") == null ? BigDecimal.ZERO : rs.getBigDecimal("newdevicesharfee"));
			}
		}

		//新增设备付费数  newdevicefees
		if (columns.contains("newdevicenums")) {
			vo.setPaydeviceAll(rs.getInt("newdevicenums"));
		}

		// 活跃设备数.
		if (columns.contains("activedevices")) {
			vo.setActiveNum(rs.getInt("activedevices"));
		}

		// 累计充值金额
		if (columns.contains("totalPayfee") || columns.contains("totalPaysharfee")) {
			//不显示分成前数据
			if (showRatio == 0) {
				vo.setTotalPayfee(rs.getBigDecimal("totalPayfee") == null ? BigDecimal.ZERO : rs.getBigDecimal("totalPayfee"));
			} else {
				vo.setTotalPayfee(rs.getBigDecimal("totalPaysharfee") == null ? BigDecimal.ZERO : rs.getBigDecimal("totalPaysharfee"));
			}
		}


		//新增付费率 regPayRatio
		if (divideNum == 0 || divideNum == null) {
			vo.setRegPayRatio(BigDecimal.ZERO);
		} else {
			vo.setRegPayRatio(new BigDecimal(rs.getInt("newdevicenums")).divide(new BigDecimal(divideNum), 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
		}

		//  新增arpu
		if (divideNum == 0 || divideNum == null) {
			vo.setRegarpu(BigDecimal.ZERO);
		} else {
			vo.setRegarpu(vo.getNewPayAmount().divide(new BigDecimal(divideNum), 2, RoundingMode.HALF_UP));
		}

		//活跃充值金额
		if (columns.contains("activetotalfee") || columns.contains("activesharfee")) {
			//不显示分成前数据
			if (showRatio == 0) {
				vo.setActivetotalfee(rs.getBigDecimal("activetotalfee") == null ? BigDecimal.ZERO : rs.getBigDecimal("activetotalfee"));
			} else {
				vo.setActivetotalfee(rs.getBigDecimal("activesharfee") == null ? BigDecimal.ZERO : rs.getBigDecimal("activesharfee"));
			}
		}

		//当周充值金额
		if (columns.contains("weektotalfee") || columns.contains("weeksharfee")) {
			//不显示分成前数据
			if (showRatio == 0) {
				vo.setWeektotalfee(rs.getBigDecimal("weektotalfee") == null ? BigDecimal.ZERO : rs.getBigDecimal("weektotalfee"));
			} else {
				vo.setWeektotalfee(rs.getBigDecimal("weeksharfee") == null ? BigDecimal.ZERO : rs.getBigDecimal("weeksharfee"));
			}
		}

		//当月充值金额
		if (columns.contains("monthtotalfee") || columns.contains("monthtotalfee")) {
			//不显示分成前数据
			if (showRatio == 0) {
				vo.setMonthtotalfee(rs.getBigDecimal("monthtotalfee") == null ? BigDecimal.ZERO : rs.getBigDecimal("monthtotalfee"));
			} else {
				vo.setMonthtotalfee(rs.getBigDecimal("monthsharfee") == null ? BigDecimal.ZERO : rs.getBigDecimal("monthsharfee"));
			}
		}

		//  活跃arpu
		if (rs.getInt("activedevices") == 0 || String.valueOf(rs.getInt("activedevices")) == null) {
			vo.setActarpu(BigDecimal.ZERO);
		} else {
			vo.setActarpu(vo.getActivetotalfee().divide(new BigDecimal(rs.getInt("activedevices")), 2, RoundingMode.HALF_UP));
		}

		//首日ROI  新增充值金额/返点后消耗
		if (cost.equals(BigDecimal.ZERO)) {
			vo.setRoi1(BigDecimal.ZERO);
		} else {
			vo.setRoi1(vo.getNewPayAmount().divide(cost, 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
		}

		//当周ROI  当周充值金额/返点后消耗
		if (cost.equals(BigDecimal.ZERO)) {
			vo.setWeekRoi(BigDecimal.ZERO);
		} else {
			vo.setWeekRoi(vo.getWeektotalfee().divide(cost, 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
		}

		//当月ROI  当月充值金额/返点后消耗
		if (cost.equals(BigDecimal.ZERO)) {
			vo.setMonthRoi(BigDecimal.ZERO);
		} else {
			vo.setMonthRoi(vo.getMonthtotalfee().divide(cost, 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
		}

		//累计充值ROI 累计充值金额/返点后消耗 allRoi
		if (cost.equals(BigDecimal.ZERO)) {
			vo.setAllRoi(BigDecimal.ZERO);
		} else {
			vo.setAllRoi(vo.getTotalPayfee().divide(cost, 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
		}
		//

		// 次留
		if (divideNum == 0 || divideNum == null) {
			vo.setRetention2Ratio(BigDecimal.ZERO);
		} else {
			vo.setRetention2Ratio(new BigDecimal(rs.getInt("num1")).divide(new BigDecimal(divideNum), 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
		}

		return vo;
	}


	private StringBuilder getSql(AdDataDto req) {
		StringBuilder sql = new StringBuilder();

		sql.append("  select \n  ");
		sql.append(this.getQueryColumnSql(req));
		sql.append(" cost, ");
		sql.append(" rudecost, ");
		sql.append(" usrnamenums, ");
		sql.append(" newdevicenums, ");
		sql.append(" newdevicefees, ");
		sql.append(" newdevicesharfee, ");
		sql.append(" activedevices, ");
		sql.append(" activepaydevices, ");
		sql.append(" activetotalfee, ");
		sql.append(" activesharfee, ");
		sql.append(" weektotalfee, ");
		sql.append(" weeksharfee, ");
		sql.append(" monthtotalfee, ");
		sql.append(" monthsharfee, ");
		sql.append(" num1, ");
		sql.append(" totalPay, ");
		sql.append(" totalPayfee, ");
		sql.append(" totalPaysharfee ");
		sql.append(" from ( \n");

		sql.append("  select \n  ");
		sql.append(this.getQueryColumnSql(req));
		sql.append(" cost, ");
		sql.append(" rudecost, ");
		sql.append(" usrnamenums, ");
		sql.append(" newdevicenums, ");
		sql.append(" newdevicefees, ");
		sql.append(" newdevicesharfee, ");
		sql.append(" activedevices, ");
		sql.append(" activepaydevices, ");
		sql.append(" activetotalfee, ");
		sql.append(" activesharfee, ");
		sql.append(" weektotalfee, ");
		sql.append(" weeksharfee, ");
		sql.append(" monthtotalfee, ");
		sql.append(" monthsharfee, ");
		sql.append(" totalPay, ");
		sql.append(" totalPayfee, ");
		sql.append(" totalPaysharfee, ");
		sql.append(" num1 ");
		sql.append(" from ( \n");

		sql.append("  select \n  ");
		sql.append(this.getQueryColumnSql(req));
		sql.append(" cost, ");
		sql.append(" rudecost, ");
		sql.append(" usrnamenums, ");
		sql.append(" newdevicenums, ");
		sql.append(" newdevicefees, ");
		sql.append(" newdevicesharfee, ");

		sql.append(" weektotalfee, ");
		sql.append(" weeksharfee, ");

		sql.append(" monthtotalfee, ");
		sql.append(" monthsharfee, ");

		sql.append(" totalPay, ");
		sql.append(" totalPayfee, ");
		sql.append(" totalPaysharfee, ");

		sql.append(" activedevices, ");
		sql.append(" activepaydevices, ");
		sql.append(" activetotalfee, ");
		sql.append(" activesharfee ");
		sql.append(" from ( \n");


		sql.append("  select \n  ");
		sql.append(this.getQueryColumnSql(req));
		sql.append(" cost, ");
		sql.append(" rudecost, ");
		sql.append(" usrnamenums, ");
		sql.append(" newdevicenums, ");
		sql.append(" newdevicefees, ");
		sql.append(" newdevicesharfee,");

		sql.append(" weektotalfee, ");
		sql.append(" weeksharfee, ");

		sql.append(" monthtotalfee, ");
		sql.append(" monthsharfee, ");

		sql.append(" totalPay, ");
		sql.append(" totalPayfee, ");
		sql.append(" totalPaysharfee ");


		sql.append(" from ( \n");

		sql.append("  select \n  ");
		sql.append(this.getQueryColumnSql(req));
		sql.append(" cost, ");
		sql.append(" rudecost, ");
		sql.append(" usrnamenums ");
		sql.append(" from ( \n");
		sql.append(this.getCostSql(req));
		sql.append(" ) d1 \n");
		sql.append(" FULL JOIN \n");
		sql.append(" ( \n");
		sql.append(this.getDeviceSql(req));
		sql.append(" )d2 \n");
		sql.append(" USING ( ");
		sql.append(this.getGroupColumnSql(req));
		sql.append("  ) \n");
		sql.append(" ) d1d2 \n");

		sql.append(" FULL JOIN \n");
		sql.append(" ( \n");
		sql.append(this.getDevicePaySql(req));
		sql.append(" )d3 \n");
		sql.append(" USING ( ");
		sql.append(this.getGroupColumnSql(req));
		sql.append("  ) \n");
		sql.append("  )d1d3 \n");

		sql.append(" FULL JOIN \n");
		sql.append(" ( \n");
		sql.append(this.getActiveDeviceSql(req));
		sql.append(" )d4 \n");
		sql.append(" USING ( ");
		sql.append(this.getGroupColumnSql(req));
		sql.append("  ) \n");
		sql.append("  )d1d4 \n");


		sql.append(" FULL JOIN \n");
		sql.append(" ( \n");
		sql.append(this.getDeviceRetetionSql(req));
		sql.append(" )d5 \n");
		sql.append(" USING ( ");
		sql.append(this.getGroupColumnSql(req));
		sql.append("  ) \n");
		sql.append("  )d1d5 \n");

		sql.append(" ORDER BY \n");
		sql.append(" period DESC \n");

		return sql;
	}


	//	消耗、返点后消耗
	private StringBuilder getCostSql(AdDataDto req) {
		StringBuilder costSql = new StringBuilder();
		costSql.append(" SELECT \n");
		//添加查询维度
		costSql.append(this.getQueryColumnSql(req));

		costSql.append(" SUM(cost) cost, --返点后消耗 \n ");
		costSql.append(" SUM(rudecost) rudecost, --消耗 \n ");
		costSql.append(" SUM(adshow) shownums, --展示数 \n ");
		costSql.append(" sum(click) clicknums --点击数 \n ");
		costSql.append(" from ( ");
		costSql.append(" SELECT  \n");
		costSql.append(" b.date  day,  \n");
		costSql.append(this.getPeriodSql(req));
		costSql.append(" IFNULL(g.pgid, 0) pgid, \n");
		costSql.append(" IFNULL(g.os, 0) os, \n");
		costSql.append(" IFNULL(reg.gameid, 0) gameid, \n");
		costSql.append(" case when reg.adid is not null and reg.adid !='' then reg.appchl else '-' end appchl,   \n");
		costSql.append(" case when reg.adid is not null and reg.adid !='' then reg.parentchl else '-' end parentchl,  \n");
		costSql.append(" case when reg.adid is not null and reg.adid !='' then reg.chl else '-' end chl,  \n");
		costSql.append(" osu.user_id investor,  \n");
		costSql.append(" osu.real_name investorName,  \n");
		costSql.append(" osu.dept_id deptId,  \n");
		costSql.append(" osd.name deptName,  \n");
		costSql.append(" IFNULL(osu.dept_group_id,0) userGroupId,"); // 组别
		costSql.append(" IFNULL(osdg.name,'') userGroupName,"); // 组别名称
		costSql.append(" b.ad_id adid,  \n");
		costSql.append(" b.adconvert adconvert,  \n");
		costSql.append(" b.ad_account ad_account,  \n");
		costSql.append(" b.adshow adshow,  \n");
		costSql.append(" b.click click,  \n");
		costSql.append(" b.rudecost rudecost,  \n");
		costSql.append(" b.cost cost \n");
		costSql.append(" from  v_adid_rebate_day b \n");
		costSql.append(" left JOIN ad_ptype reg \n");
		costSql.append(" on reg.adid = b.ad_id \n");
		costSql.append(" left JOIN v_odsmysql_wan_game g \n");
		costSql.append(" on reg.gameid=g.id \n");
		costSql.append(" LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc  -- 渠道表 \n ");
		costSql.append(" ON wpc.isdelete = 0\n");
		costSql.append(" AND wpc.parent_code = reg.parentchl \n");
		costSql.append(" AND wpc.chncode = reg.chl \n");

		costSql.append(" LEFT JOIN odsmysql_ad_account aa ON \n");
		costSql.append(" aa.advertiser_id = b.ad_account \n");
		costSql.append(" LEFT JOIN odsmysql_sys_user osu ON \n");
		costSql.append(" aa.throw_user = toString(osu.user_id) \n");
		costSql.append("  left join odsmysql_sys_dept osd \n");
		costSql.append("   on osu.dept_id = osd.dept_id \n");
		costSql.append("  left join odsmysql_sys_dept_group osdg \n");
		costSql.append("   on osu.dept_group_id = osdg.id \n");

		costSql.append(" LEFT JOIN v_odsmysql_adid vad  -- 广告计划表 \n");
		costSql.append(" ON b.ad_id = vad.adid \n");
		costSql.append(" AND b.ctype = vad.ctype \n");
		costSql.append(" WHERE 1=1 \n");
		costSql.append(" and  b.date >= ").append(req.getSTime());
		costSql.append(" and  b.date <= ").append(req.getETime());
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 广告权限
			costSql.append("  AND b.ad_account  IN (  \n");
			costSql.append(req.getAdAccounts());
			costSql.append("  ) \n");
		}

		costSql.append("  ) \n");
		costSql.append(" WHERE 1=1 \n");
		costSql.append(this.selectCondition(req, " "));
		//添加查询维度
		costSql.append(" group by ");
		costSql.append(this.getGroupColumnSql(req));
		return costSql;
	}

	//新增设备注册数、新增设备数
	private StringBuilder getDeviceSql(AdDataDto req) {
		StringBuilder uuidSql = new StringBuilder();
		uuidSql.append(" SELECT \n");

		//添加查询维度
		uuidSql.append(this.getQueryColumnSql(req));
		uuidSql.append(" COUNT(DISTINCT kid) uuidnums, --新增设备数 \n");
		uuidSql.append(" COUNT(DISTINCT dr_kid) usrnamenums --新增设备注册数 \n ");
		uuidSql.append("from ( \n ");
		uuidSql.append(" SELECT reg.day day, \n ");
		uuidSql.append(this.getPeriodSql(req));
		uuidSql.append(" reg.uuid uuid,reg.gameid gameid,reg.kid kid,reg.receivetime receivetime, ");
		uuidSql.append(" reg.pgid pgid,reg.parentchl parentchl,reg.chl chl,reg.appchl appchl, reg.os os, ad.adid adid,ad.advert_id advert_id, \n ");
		uuidSql.append(" ud.ur_kid ur_kid, \n ");
		uuidSql.append(" ud.dr_kid dr_kid, \n ");
		uuidSql.append(" wpc.manage investor, \n ");
		uuidSql.append(" osu.real_name investorName, \n ");
		uuidSql.append(" osu.dept_id deptId,  \n ");
		uuidSql.append(" IFNULL(osu.dept_group_id,0) userGroupId,"); // 组别
		uuidSql.append(" IFNULL(osdg.name,'') userGroupName,"); // 组别名称
		uuidSql.append(" osd.name deptName  \n ");
		uuidSql.append(" from thirty_game_device_reg reg \n ");
		uuidSql.append(" LEFT JOIN v_thirty_ad_device ad \n ");
		uuidSql.append(" on reg.uuid = ad.uuid \n ");
		uuidSql.append(" and reg.pgid = ad.pgid \n ");
		uuidSql.append(" and reg.day = ad.`day`  \n ");

		uuidSql.append(" LEFT JOIN ( \n ");
		uuidSql.append(" select \n ");
		uuidSql.append(" argMax(ur.kid, dr.receivetime) ur_kid, \n ");
		uuidSql.append(" argMax(dr.kid, dr.receivetime) dr_kid, \n ");
		uuidSql.append(" argMax(ur.day, dr.receivetime) day, \n ");
		uuidSql.append(" argMax(ur.uuid, dr.receivetime) uuid, \n ");
		uuidSql.append(" argMax(ur.usrname, dr.receivetime) usrname, \n ");
		uuidSql.append(" argMax(ur.gameid, dr.receivetime) gameid, \n ");
		uuidSql.append(" argMax(dr.parentchl, dr.receivetime) parentchl, \n ");
		uuidSql.append(" argMax(dr.chl, dr.receivetime) chl, \n ");
		uuidSql.append(" argMax(dr.appchl, dr.receivetime) appchl \n ");
		uuidSql.append(" from ( \n ");
		uuidSql.append(" select a.kid kid,a.`day` day,a.uuid uuid,a.usrname usrname, \n ");
		uuidSql.append(" a.gameid gameid,g.pgid pgid,a.receivetimes receivetimes \n ");
		uuidSql.append(" from v_game_account_reg a \n ");
		uuidSql.append(" left join odsmysql_wan_game g \n ");
		uuidSql.append(" on a.gameid = g.id) ur\n ");
		uuidSql.append(" LEFT join thirty_game_device_reg  dr \n ");
		uuidSql.append(" on ur.uuid = dr.uuid \n ");
		uuidSql.append(" and ur.pgid = dr.pgid \n ");

//		uuidSql.append(" and ur.day =dr.`day` \n ");

		uuidSql.append("   where 1=1  \n");
		uuidSql.append("  and  ur.receivetimes >= dr.receivetime \n");
		uuidSql.append(" and dr.day >= ").append(req.getSTime()).append(" \n ");
		uuidSql.append(" and dr.day <= ").append(req.getETime()).append(" \n ");
		uuidSql.append("  group by ur.kid  \n");
		uuidSql.append("  )ud  \n");
		uuidSql.append("  on reg.kid = ud.dr_kid  \n");
		uuidSql.append(this.getAuthSql(req, "reg"));

		uuidSql.append(") \n");

		uuidSql.append(" where 1=1 \n");

		uuidSql.append(" and day >= ").append(req.getSTime()).append(" \n ");
		uuidSql.append(" and day <= ").append(req.getETime()).append(" \n ");

		uuidSql.append(this.selectCondition(req, " "));

		uuidSql.append("  group by \n");
		uuidSql.append(this.getGroupColumnSql(req));
		return uuidSql;
	}

	//新增充值金额和新增设备付费数,当周充值金额，当月充值金额，累计充值金额
	private StringBuilder getDevicePaySql(AdDataDto req) {

		StringBuilder UuidPaySql = new StringBuilder();
		UuidPaySql.append(" SELECT \n");
		//添加查询维度
		UuidPaySql.append(this.getQueryColumnSql(req));
		UuidPaySql.append(" COUNT(DISTINCT paykid1) newdevicenums, ---新增充值人数 \n");
		UuidPaySql.append(" SUM(worth1) newdevicefees,   ----新增充值金额 \n ");
		UuidPaySql.append(" SUM(sharfee1) newdevicesharfee,   ----新增充值金额分成后 \n ");

		UuidPaySql.append(" SUM(worth7) weektotalfee, ---当周充值金额 \n");
		UuidPaySql.append(" SUM(sharfee7) weeksharfee,   ----当周充值金额分成后  \n ");

		UuidPaySql.append(" SUM(worth30) monthtotalfee, ---当月充值金额 \n");
		UuidPaySql.append(" SUM(sharfee30) monthsharfee,   ----当月充值金额 分成后 \n ");

		UuidPaySql.append(" COUNT(DISTINCT regkid) totalPay,   ----累计充值设备数 \n ");
		UuidPaySql.append(" SUM(fee+givemoney) totalPayfee,   ----累计充值金额 \n ");
		UuidPaySql.append(" SUM(sharfee) totalPaysharfee   ----累计充值金额 分成后 \n ");

		UuidPaySql.append("from ( \n ");
		UuidPaySql.append(" SELECT \n ");
		UuidPaySql.append(" argMax(reg.day,reg.receivetime) regDay, \n ");
		UuidPaySql.append(" regDay AS day, \n ");
		UuidPaySql.append(" parseDateTimeBestEffort(toString(day)) dayDate, \n ");
		UuidPaySql.append(" toString(toYear(dayDate)) yearStr, \n ");
		UuidPaySql.append(" toMonth(dayDate) monthInt, \n ");
		UuidPaySql.append(" toYearWeek(dayDate,3) weekInt, \n ");
//		UuidPaySql.append(" concat(yearStr,'-',IF(10>monthInt,'0',''),toString(monthInt),'月') AS monthStr,concat(toString(weekInt),'周') AS week, \n ");

		UuidPaySql.append("  concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS month,  \n");
		UuidPaySql.append("  concat(IF(10>weekInt, '0', ''), toString(weekInt), '周') AS week,  \n");

		UuidPaySql.append(" yearStr as year, \n ");

		UuidPaySql.append(req.getPeriod()).append(" as period, ");
		UuidPaySql.append(" argMax(reg.pgid,reg.receivetime) pgid, \n ");
		UuidPaySql.append(" argMax(reg.gameid,reg.receivetime) AS gameid, \n ");
		UuidPaySql.append(" argMax(reg.os,reg.receivetime) AS os, \n ");
		UuidPaySql.append(" argMax(reg.parentchl,reg.receivetime) parentchl, \n ");
		UuidPaySql.append(" argMax(reg.chl,reg.receivetime) chl, \n ");
		UuidPaySql.append(" argMax(reg.appchl,reg.receivetime) appchl, \n ");
		UuidPaySql.append(" argMax(wpc.manage,reg.receivetime) investor, \n ");
		UuidPaySql.append(" argMax(osu.real_name,reg.receivetime) investorName, \n ");
		UuidPaySql.append(" argMax(osu.dept_id,reg.receivetime) deptId,  \n ");

		UuidPaySql.append(" IFNULL(argMax(osu.dept_group_id,reg.receivetime),0) userGroupId,\n"); // 组别
		UuidPaySql.append(" IFNULL(argMax(osdg.name,reg.receivetime),'') userGroupName, \n"); // 组别名称

		UuidPaySql.append(" argMax(osd.name,reg.receivetime) deptName,\n"); // 组别
		UuidPaySql.append(" argMax(ad.adid,reg.receivetime) adidTmp, \n"); // 组别名称

		UuidPaySql.append(" argMax(ad.adname,reg.receivetime) adidName,  \n ");
		UuidPaySql.append(" argMax(ad.adaccount,reg.receivetime) adAccount, \n ");
		UuidPaySql.append(" (case when adidTmp is not null THEN adidTmp else '' end) as adid, \n ");
		UuidPaySql.append(" rc.kid rckid, \n ");
		UuidPaySql.append(" argMax(reg.kid,reg.receivetime) regkid, \n ");
		UuidPaySql.append(" argMax(rc.day,reg.receivetime) payday, \n ");
		UuidPaySql.append(" parseDateTimeBestEffort(toString(payday)) paydayDate, \n ");
		UuidPaySql.append(" toString(toYear(paydayDate)) payYearStr, \n ");
		UuidPaySql.append(" toMonth(paydayDate) payMonthInt, \n ");
		UuidPaySql.append(" toYearWeek(paydayDate,3) payWeekInt, \n ");
		UuidPaySql.append(" concat(payYearStr,'-',IF(10>payMonthInt,'0',''),toString(payMonthInt),'月') AS payMonthStr, \n ");
		UuidPaySql.append(" dateDiff('day',parseDateTimeBestEffort(toString(day)),today() ) as cur_diff, \n ");
		UuidPaySql.append(" dateDiff('day',parseDateTimeBestEffort(toString(day)),parseDateTimeBestEffort(toString(payday))) as pay_diff, \n ");
		UuidPaySql.append(" argMax(rc.uuid,reg.receivetime) AS payuuid, \n ");
		UuidPaySql.append(" argMax(rc.fee,reg.receivetime) fee, \n ");
		UuidPaySql.append(" argMax(rc.givemoney,reg.receivetime) givemoney,\n ");
		UuidPaySql.append(" argMax(rc.sharfee,reg.receivetime) sharfee, \n ");
		UuidPaySql.append(" (case when pay_diff = 0 then fee + givemoney else 0 end) worth1, \n ");
		UuidPaySql.append(" (case when pay_diff = 0 then sharfee else 0 end) sharfee1, \n ");
		UuidPaySql.append(" (case when pay_diff = 0 then regkid else null end) paykid1, \n ");
		UuidPaySql.append(" (case when weekInt = payWeekInt then fee + givemoney else 0 end) worth7, \n ");
		UuidPaySql.append(" (case when weekInt = payWeekInt then sharfee else 0 end) sharfee7, \n ");
		UuidPaySql.append(" (case when weekInt = payWeekInt then regkid else null end) paykid7, \n ");
		UuidPaySql.append(" (case when month = payMonthStr then fee + givemoney else 0 end) worth30, \n ");
		UuidPaySql.append(" (case when month = payMonthStr then sharfee else 0 end) sharfee30, \n ");
		UuidPaySql.append(" (case when month = payMonthStr then regkid else null end) paykid30, \n ");
		UuidPaySql.append(" argMax(rc.createtime,reg.receivetime) createtime \n ");

		UuidPaySql.append(" from ");
		UuidPaySql.append(" v_original_user_recharge_share rc \n ");
		UuidPaySql.append("  LEFT join thirty_game_device_reg reg on \n ");
		UuidPaySql.append("  rc.uuid = reg.uuid  \n ");
		UuidPaySql.append(" and rc.pgid = reg.pgid  \n ");
		UuidPaySql.append(" LEFT JOIN v_thirty_ad_device ad on \n ");
		UuidPaySql.append(" reg.uuid = ad.uuid \n ");
		UuidPaySql.append(" and reg.day = ad.day \n ");
		UuidPaySql.append(" and reg.pgid = ad.pgid \n ");
		UuidPaySql.append(" LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON \n ");
		UuidPaySql.append(" wpc.isdelete = 0 \n ");
		UuidPaySql.append(" AND wpc.parent_code = reg.parentchl \n ");
		UuidPaySql.append(" AND wpc.chncode = reg.chl \n ");
		UuidPaySql.append(" LEFT JOIN odsmysql_sys_user osu ON \n ");
		UuidPaySql.append(" wpc.manage = osu.user_id \n ");
		UuidPaySql.append(" left join odsmysql_sys_dept osd \n ");
		UuidPaySql.append(" on osu.dept_id = osd.dept_id \n ");
		UuidPaySql.append("  left join odsmysql_sys_dept_group osdg \n");
		UuidPaySql.append("   on osu.dept_group_id = osdg.id \n");

		UuidPaySql.append(" where 1=1 \n ");
		UuidPaySql.append("   and reg.`day` >= ").append(req.getSTime()).append("\n");
		UuidPaySql.append("   and reg.`day`<=  ").append(req.getETime()).append("\n");
		UuidPaySql.append("   AND rc.`day` >= reg.`day`    \n ");
		UuidPaySql.append("   group by rc.kid  )    \n ");

		UuidPaySql.append("                 where 1=1");

		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			UuidPaySql.append(" AND \n");
			UuidPaySql.append(" investor IN ( -- 管理的账号 \n");
			UuidPaySql.append(req.getUserIds());
			UuidPaySql.append(" ) \n");
		}
		//查询条件
		UuidPaySql.append(this.selectCondition(req, " "));
		// 分组
		UuidPaySql.append("   group by \n ");

		UuidPaySql.append(this.getGroupColumnSql(req));

		return UuidPaySql;
	}


	private StringBuilder getActiveDeviceSql(AdDataDto req) {
		StringBuilder activeDeviceSql = new StringBuilder();
		activeDeviceSql.append(" SELECT \n ");
		activeDeviceSql.append(this.getQueryColumnSql(req));
		activeDeviceSql.append("d41.activepaydevices activepaydevices,\n" +
				"d41.activetotalfee activetotalfee,\n" +
				"d41.activesharfee activesharfee,\n" +
				"d42.activeNum activedevices\n");
		activeDeviceSql.append(" FROM\n" +
				"( \n ");
		activeDeviceSql.append("select\n");
		activeDeviceSql.append(this.getQueryColumnSql(req));
		activeDeviceSql.append("COUNT(DISTINCT regkid) activepaydevices,\n");
		activeDeviceSql.append("SUM(fee+givemoney) activetotalfee,\n");
		activeDeviceSql.append("SUM(sharfee) activesharfee\n");
		activeDeviceSql.append(" from\n" +
				"\t( \n ");
		activeDeviceSql.append(" SELECT ");
		activeDeviceSql.append(" argMax(reg.day,reg.receivetime) regDay,\n" +
				"\t\targMax(rc.day,reg.receivetime) payday,payday AS day,\n ");
		activeDeviceSql.append(this.getPeriodSql(req));
		activeDeviceSql.append(" argMax(reg.pgid,reg.receivetime) pgid,\n" +
				"\t\targMax(reg.gameid,reg.receivetime) AS gameid,\n" +
				"\t\targMax(reg.os,reg.receivetime) AS os,\n" +
				"\t\targMax(reg.parentchl,reg.receivetime) parentchl,\n" +
				"\t\targMax(reg.chl,reg.receivetime) chl,\n" +
				"\t\targMax(reg.appchl,reg.receivetime) appchl,\n ");
		activeDeviceSql.append(" argMax(wpc.manage,reg.receivetime) investor,\n" +
				"\t\targMax(osu.dept_id,reg.receivetime) deptId,\n" +
				"        argMax(osd.name, reg.receivetime) deptName, \n" +
				"\t\tIFNULL(argMax(osu.dept_group_id,reg.receivetime),0) userGroupId,\n" + // 组别
				"\t\tIFNULL(argMax(osdg.name,reg.receivetime),'') userGroupName,\n" + // 组别名称
				"        argMax(osu.real_name, reg.receivetime) investorName,   \n ");
		activeDeviceSql.append(" rc.kid rckid,\n" +
				"\t\targMax(reg.kid,reg.receivetime) regkid,\n" +
				"\t\targMax(rc.uuid,reg.receivetime) AS payuuid,\n" +
				"\t\targMax(rc.fee,reg.receivetime) fee,\n" +
				"\t\targMax(rc.givemoney,reg.receivetime) givemoney,\n" +
				"\t\targMax(rc.sharfee,reg.receivetime) sharfee   \n ");
		activeDeviceSql.append(" from\n" +
				"\t\tv_original_user_recharge_share rc\n" +
				"\n" +
				"\tINNER JOIN thirty_game_device_reg reg on\n" +
				"\t\trc.uuid = reg.uuid\n" +
				"\t\tand rc.pgid = reg.pgid \n ");

		// 权限
		activeDeviceSql.append(" LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc  -- 渠道表 \n");
		activeDeviceSql.append("  ON wpc.isdelete = 0 \n");
		activeDeviceSql.append("  AND wpc.parent_code = reg.parentchl \n");
		activeDeviceSql.append("  AND wpc.chncode = reg.chl \n");

		activeDeviceSql.append("  LEFT JOIN odsmysql_sys_user osu ON \n");
		activeDeviceSql.append("  wpc.manage = osu.user_id \n");
		activeDeviceSql.append("  left join odsmysql_sys_dept osd \n");
		activeDeviceSql.append("   on osu.dept_id = osd.dept_id \n");
		activeDeviceSql.append("  left join odsmysql_sys_dept_group osdg \n");
		activeDeviceSql.append("   on osu.dept_group_id = osdg.id \n");
//		activeDeviceSql.append(this.getAuthSql(req, "reg"));
		activeDeviceSql.append(" where 1=1 ");
		activeDeviceSql.append(" and rc.`day` >= ").append(req.getSTime()).append(" \n");
		activeDeviceSql.append(" and rc.`day` <= ").append(req.getETime()).append(" \n");
		activeDeviceSql.append(" and rc.`day` >= reg.`day`").append(" \n");

		activeDeviceSql.append(" group by\n" +
				"\t\trc.kid ) a \n ");
		activeDeviceSql.append("  WHERE 1 = 1\n");

		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			activeDeviceSql.append(" AND \n");
			activeDeviceSql.append(" investor IN ( -- 管理的账号 \n");
			activeDeviceSql.append(req.getUserIds());
			activeDeviceSql.append(" ) \n");
		}

		//筛选条件
		activeDeviceSql.append(this.selectCondition(req, " "));

		activeDeviceSql.append(" group by  \n");
		activeDeviceSql.append(this.getGroupColumnSql(req));

		activeDeviceSql.append(" )d41 \n ");
		activeDeviceSql.append(" FULL JOIN\n" +
				"( \n ");
		activeDeviceSql.append(" SELECT\n");
		activeDeviceSql.append(this.getQueryColumnSql(req));
		activeDeviceSql.append("  uniqExact(regkid) activeNum,\n");
		activeDeviceSql.append(" uniqExact(IF(login_uuid IS NULL,null,regkid)) activeLoginNum\n");

		activeDeviceSql.append("from\n" +
				"\t( ");
		activeDeviceSql.append(" select\n" +
				"\t\targMax(reg.day,reg.receivetime) regDay,\n" +
				"        argMax(ub.day,reg.receivetime) activeDay,\n" +
				"\t\tactiveDay AS day, \n ");
		activeDeviceSql.append(this.getPeriodSql(req));
		activeDeviceSql.append(" \t\targMax(reg.pgid,reg.receivetime) pgid,\n" +
				"\t\targMax(reg.gameid,reg.receivetime) AS gameid,\n" +
				"\t\targMax(reg.os,reg.receivetime) AS os,\n" +
				"\t\targMax(reg.parentchl,reg.receivetime) parentchl,\n" +
				"\t\targMax(reg.chl,reg.receivetime) chl,\n" +
				"\t\targMax(reg.appchl,reg.receivetime) appchl, \n ");
		activeDeviceSql.append(" argMax(wpc.manage,reg.receivetime) investor,\n" +
				"\t\targMax(osu.dept_id,reg.receivetime) deptId,\n" +
				"\t\tIFNULL(argMax(osu.dept_group_id,reg.receivetime),0) userGroupId,\n" + // 组别
				"\t\tIFNULL(argMax(osdg.name,reg.receivetime),'') userGroupName,\n" + // 组别名称
				"\t\targMax(osd.name, reg.receivetime) deptName, \n" +
				"        argMax(osu.real_name, reg.receivetime) investorName, \n ");
		activeDeviceSql.append("  ub.kid ubkid,\n" +
				"\t\targMax(reg.kid,reg.receivetime) regkid,\n" +
				"\t\targMax(ub.uuid,reg.receivetime) AS uuid,\n" +
				"\t\targMax(ul.uuid,reg.receivetime) AS login_uuid \n ");
		activeDeviceSql.append(" \tfrom\n" +
				"\t\tuser_behavior ub\n" +
				"\tINNER join odsmysql_wan_game g on\n" +
				"\t\tub.gameid = g.id\n" +
				"\tINNER join thirty_game_device_reg reg on\n" +
				"\t\tub.uuid = reg.uuid\n" +
				"\t\tand g.pgid = reg.pgid \n ");


		// 权限

		activeDeviceSql.append(" LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc  -- 渠道表 \n");
		activeDeviceSql.append("  ON wpc.isdelete = 0 \n");
		activeDeviceSql.append("  AND wpc.parent_code = reg.parentchl \n");
		activeDeviceSql.append("  AND wpc.chncode = reg.chl \n");

		activeDeviceSql.append("  LEFT JOIN odsmysql_sys_user osu ON \n");
		activeDeviceSql.append("  wpc.manage = osu.user_id \n");
		activeDeviceSql.append("  left join odsmysql_sys_dept osd \n");
		activeDeviceSql.append("   on osu.dept_id = osd.dept_id \n");
		activeDeviceSql.append("  left join odsmysql_sys_dept_group osdg \n");
		activeDeviceSql.append("   on osu.dept_group_id = osdg.id \n");
		activeDeviceSql.append("  \tLEFT JOIN user_login ul on\n" +
				"\t\treg.uuid = ul.uuid\n" +
				"\t\tand ub.gameid = ul.gameid\n" +
				"\t\tand ub.`day` = ul.`day` \n ");


//		activeDeviceSql.append(this.getAuthSql(req, "reg"));
		activeDeviceSql.append(" where 1=1 ");

		activeDeviceSql.append(" and ub.`day` >= ").append(req.getSTime()).append(" \n");
		activeDeviceSql.append(" and ub.`day` <= ").append(req.getETime()).append(" \n");
		activeDeviceSql.append(" and reg.`day` <= ").append(req.getETime()).append(" \n");
		activeDeviceSql.append(" and ub.receivetime >= reg.receivetime ").append(" \n");

		activeDeviceSql.append("  GROUP BY\n" +
				"\t\tub.kid ) a \n ");
		activeDeviceSql.append("  where\n" +
				"\t1 = 1 \n ");
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			activeDeviceSql.append(" AND ");
			activeDeviceSql.append(" investor IN ( -- 管理的账号 \n");
			activeDeviceSql.append(req.getUserIds());
			activeDeviceSql.append(" ) \n");
		}

		//筛选条件
		activeDeviceSql.append(this.selectCondition(req, " "));

		activeDeviceSql.append(" group by  \n");
		activeDeviceSql.append(this.getGroupColumnSql(req));

		activeDeviceSql.append(" )d42 \n ");
		activeDeviceSql.append(" USING ( ");
		activeDeviceSql.append(this.getGroupColumnSql(req));
		activeDeviceSql.append("  ) \n");
		return activeDeviceSql;
	}


	//次留计算
	private StringBuilder getDeviceRetetionSql(AdDataDto req) {
		StringBuilder DeviceRetetionSql = new StringBuilder();
		DeviceRetetionSql.append(" SELECT \n");
		DeviceRetetionSql.append(this.getQueryColumnSql(req));
		DeviceRetetionSql.append(" uniqExact((multiIf(niff<1 , NULL, diff = 1 , a.regkid, NULL)))  num1 \n");
		DeviceRetetionSql.append(" from ( \n");
		DeviceRetetionSql.append(" SELECT \n");
		DeviceRetetionSql.append(" regDay day, \n");
		DeviceRetetionSql.append(this.getPeriodSql(req));
		DeviceRetetionSql.append(" activeDay, \n");
		DeviceRetetionSql.append(" uuid, \n");
		DeviceRetetionSql.append(" adid,  \n");
		DeviceRetetionSql.append(" adaccount, gameid, os, pgid, regkid, parentchl, chl, appchl, \n");
		DeviceRetetionSql.append(" investor, deptId, userGroupId, userGroupName,deptName, investorName, \n");
		DeviceRetetionSql.append(" dateDiff('day', parseDateTimeBestEffort(toString(regDay)), parseDateTimeBestEffort(toString(activeDay))) as diff,  \n");
		DeviceRetetionSql.append(" dateDiff('day', parseDateTimeBestEffort(toString(regDay)), today()) as niff  \n ");
		DeviceRetetionSql.append(" FROM (   \n");
		DeviceRetetionSql.append(" select   \n");
		DeviceRetetionSql.append(" argMax(reg.day, reg.receivetime) regDay,   \n");
		DeviceRetetionSql.append(" argMax(ub.day, reg.receivetime) activeDay,  \n");
		DeviceRetetionSql.append(" argMax(ub.uuid, reg.receivetime) AS uuid,   \n");
		DeviceRetetionSql.append(" argMax(reg.gameid, reg.receivetime) AS gameid,   \n");
		DeviceRetetionSql.append(" argMax(reg.os, reg.receivetime) AS os,   \n");
		DeviceRetetionSql.append(" argMax(reg.pgid, reg.receivetime) pgid,   \n");
		DeviceRetetionSql.append(" argMax(reg.kid, reg.receivetime) regkid,   \n");
		DeviceRetetionSql.append(" argMax(reg.parentchl, reg.receivetime) parentchl,   \n");
		DeviceRetetionSql.append(" argMax(reg.chl, reg.receivetime) chl,   \n");
		DeviceRetetionSql.append(" argMax(ad.adid, reg.receivetime) adid,   \n");
		DeviceRetetionSql.append(" argMax(ad.adaccount, reg.receivetime) adaccount,   \n");
		DeviceRetetionSql.append(" argMax(ad.advert_id, reg.receivetime) advert_id,   \n");

		DeviceRetetionSql.append(" argMax(wpc.manage,reg.receivetime) investor, \n");
		DeviceRetetionSql.append(" argMax(osu.dept_id, reg.receivetime) deptId, \n");
		DeviceRetetionSql.append(" IFNULL(argMax(osu.dept_group_id, reg.receivetime),0) userGroupId, \n");
		DeviceRetetionSql.append(" IFNULL(argMax(osdg.name, reg.receivetime),'') userGroupName, \n");
		DeviceRetetionSql.append(" argMax(osd.name, reg.receivetime) deptName, \n");
		DeviceRetetionSql.append(" argMax(osu.real_name, reg.receivetime) investorName, \n");


		DeviceRetetionSql.append(" argMax(reg.appchl, reg.receivetime) appchl   \n");
		DeviceRetetionSql.append(" from user_login ub   \n");
		DeviceRetetionSql.append(" left join odsmysql_wan_game g   \n");
		DeviceRetetionSql.append(" on ub.gameid = g.id   \n");
		DeviceRetetionSql.append("  LEFT join thirty_game_device_reg reg   \n");
		DeviceRetetionSql.append(" on ub.uuid = reg.uuid   \n");
		DeviceRetetionSql.append(" and g.pgid = reg.pgid  \n");
		DeviceRetetionSql.append(" LEFT JOIN v_thirty_ad_device ad  \n");
		DeviceRetetionSql.append(" on reg.uuid = ad.uuid  \n");
		DeviceRetetionSql.append(" and reg.pgid = ad.pgid  \n");
		DeviceRetetionSql.append(" and reg.`day` = ad.`day`  \n");

		// 权限关联表
		DeviceRetetionSql.append(" LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc  -- 渠道表 \n");
		DeviceRetetionSql.append("  ON wpc.isdelete = 0 \n");
		DeviceRetetionSql.append("  AND wpc.parent_code = reg.parentchl \n");
		DeviceRetetionSql.append("  AND wpc.chncode = reg.chl \n");

		DeviceRetetionSql.append("  LEFT JOIN odsmysql_sys_user osu ON \n");
		DeviceRetetionSql.append("  wpc.manage = osu.user_id \n");
		DeviceRetetionSql.append("  left join odsmysql_sys_dept osd \n");
		DeviceRetetionSql.append("   on osu.dept_id = osd.dept_id \n");
		DeviceRetetionSql.append("  left join odsmysql_sys_dept_group osdg \n");
		DeviceRetetionSql.append("   on osu.dept_group_id = osdg.id \n");

//		DeviceRetetionSql.append(this.getAuthSql(req, "ad"));
		DeviceRetetionSql.append(" where 1=1 ");

		DeviceRetetionSql.append(" and reg.day >= ").append(req.getSTime());
		DeviceRetetionSql.append(" and reg.day <= ").append(req.getETime());
		DeviceRetetionSql.append(" and ub.receivetime >= reg.receivetime ");
		DeviceRetetionSql.append(" and ub.`day`>= ").append(req.getSTime());
		DeviceRetetionSql.append(" GROUP BY ub.kid) ");
		DeviceRetetionSql.append(" )a  ");

		DeviceRetetionSql.append(" where 1=1  ");

		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			DeviceRetetionSql.append(" AND \n");
			DeviceRetetionSql.append(" a.investor IN ( -- 管理的账号 \n");
			DeviceRetetionSql.append(req.getUserIds());
			DeviceRetetionSql.append(" ) \n");
		}

		// 筛选条件
		DeviceRetetionSql.append(this.selectCondition(req, " "));

		DeviceRetetionSql.append(" GROUP BY ");
		DeviceRetetionSql.append(this.getGroupColumnSql(req));
		return DeviceRetetionSql;
	}


	//权限关联表
	private StringBuilder getAuthSql(AdDataDto req, String bieming) {
		StringBuilder authSql = new StringBuilder();
		// 权限关联表
		authSql.append(" LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc  -- 渠道表 \n");
		authSql.append("  ON wpc.isdelete = 0 \n");
		authSql.append("  AND wpc.parent_code = reg.parentchl \n");
		authSql.append("  AND wpc.chncode = reg.chl \n");

		authSql.append("  LEFT JOIN odsmysql_sys_user osu ON \n");
		authSql.append("  wpc.manage = osu.user_id \n");
		authSql.append("  left join odsmysql_sys_dept osd \n");
		authSql.append("   on osu.dept_id = osd.dept_id \n");
		authSql.append("  left join odsmysql_sys_dept_group osdg \n");
		authSql.append("   on osu.dept_group_id = osdg.id \n");
//		authSql.append("  LEFT JOIN v_odsmysql_adid vad  -- 广告计划表 \n");
//		authSql.append("on ").append(bieming).append(".adid = vad.adid ");
		authSql.append(" WHERE 1=1 \n");

		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			authSql.append(" AND \n");
			authSql.append(" wpc.manage IN ( -- 管理的账号 \n");
			authSql.append(req.getUserIds());
			authSql.append(" ) \n");
		}
		return authSql;
	}

	//周期 周月
	private StringBuilder getPeriodSql(AdDataDto req) {

		StringBuilder periodSq = new StringBuilder();
		periodSq.append(" parseDateTimeBestEffort(toString(day)) dayDate,  \n");
		periodSq.append(" toString(toYear(dayDate)) yearStr,  \n");
		periodSq.append("  toMonth(dayDate) monthInt,  \n");
		periodSq.append("  toYearWeek(dayDate,3) weekInt,  \n");
		periodSq.append("  concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS month,  \n");
		periodSq.append("  concat(IF(10>weekInt, '0', ''), toString(weekInt), '周') AS week,  \n");
		periodSq.append(" yearStr as year, ");
		periodSq.append(req.getPeriod()).append(" as period, ");

		return periodSq;
	}

	//类别参数
	private StringBuilder getQueryColumnSql(AdDataDto req) {
		StringBuilder sql = new StringBuilder();
		StringBuilder periodColumnSql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		periodColumnSql.append(" period ");
		queryColumnSql.append(",");
		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("deptName");
				queryColumnSql.append(",");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("investorName");
				queryColumnSql.append(",");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("userGroupName");
				queryColumnSql.append(",");
			}
			queryColumnSql.append(queryColumn);
			queryColumnSql.append(",");
		}
		sql.append(periodColumnSql).append(queryColumnSql);
		return sql;
	}

	//分组条件
	private StringBuilder getGroupColumnSql(AdDataDto req) {
		String queryColumn = req.getQueryColumn();

		StringBuilder sql = new StringBuilder();
		StringBuilder periodColumnSql = new StringBuilder();
		StringBuilder groupColumnSql = new StringBuilder();
		periodColumnSql.append(" period ");

		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				groupColumnSql.append(",");
				groupColumnSql.append("deptName");

			}
			if (queryColumn.contains("investor")) {
				groupColumnSql.append(",");
				groupColumnSql.append("investorName");
			}
			if (queryColumn.contains("userGroupId")) {
				groupColumnSql.append(",");
				groupColumnSql.append("userGroupName");

			}
			groupColumnSql.append(",");
			groupColumnSql.append(queryColumn);
		}
		sql.append(periodColumnSql).append(groupColumnSql);
		return sql;
	}

	//筛选条件
	public String selectCondition(AdDataDto req, String bieming) {
		StringBuilder sqlCondition = new StringBuilder();
		//系统
		Integer os = req.getOs();
		//主游戏
		String pgidArr = req.getPgidArr();
		//子游戏
		String gameidArr = req.getGameidArr();
		//主渠道
		String parentchlArr = req.getParentchlArr();
		//分包渠道
		String appchlArr = req.getAppchlArr();
		//部门
		String deptIdArr = req.getDeptIdArr();
		//投放人
		String investorArr = req.getInvestorArr();

		//日期
		Long sTime = req.getSTime();
		Long eTime = req.getETime();
		if (StringUtils.isBlank(bieming)) {

			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append("  AND deptId IN (").append(deptIdArr).append(")");
			}

			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append(" AND investor IN (").append(investorArr).append(")");
			}

//			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {
//				sqlCondition.append(" and day  >= ").append(sTime);
//				sqlCondition.append(" and day  <= ").append(eTime);
//			}
			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and pgid  = " + pgidArr);
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and gameid in (" + gameidArr + ")");
				} else {
					sqlCondition.append(" and gameid  = " + gameidArr);
				}
			}
			if (Objects.nonNull(os)) {

				sqlCondition.append(" and os  = ").append(os);
			}
			return sqlCondition.toString();
		} else {
//			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {
//				sqlCondition.append(" and ").append(bieming).append(".day  >= ").append(sTime);
//				sqlCondition.append(" and ").append(bieming).append(".day  <= ").append(eTime);
//			}


			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".deptId IN (").append(deptIdArr).append(")");
			}

			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".investor IN (").append(investorArr).append(")");
			}

			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".pgid  = " + pgidArr);
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".gameid in (" + gameidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".gameid  = " + gameidArr);
				}
			}
			if (Objects.nonNull(os)) {

				sqlCondition.append(" and ").append(bieming).append(".os  = '" + os + "'");
			}
			return sqlCondition.toString();
		}
	}

}
